﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;

namespace Autenticazione.DB
{
    public class DB
    {
        private string strConn = string.Empty;

        private SqlConnection Conn
        {
            get
            {
                if (string.IsNullOrEmpty(strConn))
                    return null;
                else
                    return new SqlConnection(strConn);
            }
        }

        public DB()
        {
            strConn = System.Configuration.ConfigurationManager.AppSettings["SqlConnectionString"];
        }

        public DataTable GetTable(string strSQL)
        {
            DataTable tblCoach = new DataTable();
            SqlConnection conn = this.Conn;

            SqlDataAdapter ad = null;

            try
            {
                conn.Open();
                ad = new SqlDataAdapter(strSQL, conn);
                ad.Fill(tblCoach);
            }
            catch (SqlException exc)
            {
                throw new Exception(exc.Message);
            }
            finally
            {
                conn.Close();
            }

            return tblCoach;
        }

        public SqlDataReader Reader(string strSQL)
        {
            SqlConnection conn = this.Conn;
            SqlDataReader reader = null;
            SqlCommand comm = null;

            if (conn == null)
                return null;

            try
            {
                conn.Open();
                comm = new SqlCommand(strSQL, conn);
                reader = comm.ExecuteReader(System.Data.CommandBehavior.CloseConnection);

            }
            catch (SqlException exc)
            {
                conn.Close();
                throw new Exception(exc.Message);
            }
            //finally
            //{
            //    conn.Close();
            //}

            return reader;
             
        }

        public void CloseConnection()
        {
            SqlConnection conn = this.Conn;
            conn.Close();
        }

        public bool ExecuteNonQuery(string strSQL)
        {
            bool executed = true;
            SqlConnection conn = this.Conn;
            SqlCommand comm = null;

            if (conn == null)
                return false;

            try
            {
                conn.Open();
                comm = new SqlCommand(strSQL, conn);
                comm.ExecuteNonQuery();
            }
            catch (SqlException exc)
            {
                executed = false;
                throw new Exception(exc.Message);
            }
            finally
            {
                conn.Close();
            }

            return executed;
        }

        public int ExecuteScalar(string strSQL)
        {
            SqlConnection conn = this.Conn;
            SqlCommand comm = null;
            int n = 0;

            if (conn == null)
                return n;

            try
            {
                conn.Open();
                comm = new SqlCommand(strSQL, conn);
                n = (int)comm.ExecuteScalar();
            }
            catch (SqlException exc)
            {
                throw new Exception(exc.Message);
            }
            finally
            {
                conn.Close();
            }

            return n;
        }
    }
}